-- Desc: BO工序物料信息
alter PROCEDURE
    getBoProcessMaterialInfo @begingmt_create DATETIME,
                             @endgmt_create DATETIME,
                             @PageIndex INT,
                             @PageSize INT,
                             @TotalCount INT OUTPUT,
                             @BoStatus NVARCHAR(255),
                             @item_code NVARCHAR(255),
                             @khdm VARCHAR(50),
                             @team NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    -- 计算分页的起始位置
    DECLARE @Offset INT = (@PageIndex - 1) * @PageSize;
--------------------------------------------------------------------------------------------
    SELECT a.batch_number    AS 工单号,
           mes_type.name     AS MES订单类型,
           a.item_code       AS Item_Code,
           a.customer_code   AS 客户代码,
           a.customer_name   AS 客户名称,
           a.billing_date    AS 开单日期,
           a.gmt_modified    AS 工单修改时间,
           b.sn              AS 序号,
           mt.name   AS 物料类型,
           b.material_code   AS 物料编码,
           b.material_usage  AS 物料用量,
           b.material_remark AS 物料备注,
           b.paper_open      AS 纸张开数,
           b.open_paper_info AS 开纸信息,
           b.wh_org          AS WH_Org,
           b.gmt_create      AS 创建时间,
           b.gmt_modified    AS 报表字段名称17,
           b.usage_unit      AS 用量单位,
           wlzt.name         AS BO物料状态,
           c.process_name    AS 工序名称
    FROM mes_batch_order a
             LEFT JOIN
         mes_bo_process_material b ON a.id = b.bo_id
             left join dbo.mes_bo_process c on c.id = b.bo_process_id
             left join sys_dict mes_type on
        a.mes_order_type = mes_type.value and mes_type.type = 'MES_ORDER_TYPE'
             left join sys_dict wlzt on wlzt.value = a.status and wlzt.type =
                                                                  'BO_MATERIAL_STATUS'
             left join  dbo.sys_material_type mt on mt.id = b.material_type


------------------------------------------------------------------------------------------------------


    WHERE (@begingmt_create IS NULL OR a.gmt_create >= @begingmt_create)
      AND (@endgmt_create IS NULL OR a.gmt_create <= @endgmt_create)
      AND (@BoStatus IS NULL OR a.status in (SELECT value FROM STRING_SPLIT(@BoStatus, ',')))
      AND (@item_code IS NULL OR a.item_code = @item_code)
      AND (@khdm IS NULL OR a.customer_code = @khdm)
      and (@team IS NULL OR a.team = @team)


    order by a.gmt_create desc
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

    SELECT @TotalCount = COUNT(1)

    ---------------------------------------------------------------------

    FROM mes_batch_order a
             LEFT JOIN
         mes_bo_process_material b ON a.id = b.bo_id
             left join dbo.mes_bo_process c on c.id = b.bo_process_id
             left join sys_dict mes_type on
        a.mes_order_type = mes_type.value and mes_type.type = 'MES_ORDER_TYPE'
             left join sys_dict wlzt on wlzt.value = a.status and wlzt.type =
                                                                  'BO_MATERIAL_STATUS'
             left join  dbo.sys_material_type mt on mt.id = b.material_type
    -------------------------------------------

    WHERE (@begingmt_create IS NULL OR a.gmt_create >= @begingmt_create)
      AND (@endgmt_create IS NULL OR a.gmt_create <= @endgmt_create)
      AND (@BoStatus IS NULL OR a.status in (SELECT value FROM STRING_SPLIT(@BoStatus, ',')))
      AND (@item_code IS NULL OR a.item_code = @item_code)
      AND (@khdm IS NULL OR a.customer_code = @khdm)
      and (@team IS NULL OR a.team = @team)

END